# -*- coding: utf-8 -*-
#Importing connection 
from pycube.core import query, connection
import psycopg2
from re import match
from sys import exit


"""

QUERY maker for POSTGRESSQL

"""




def sql_escape_chars( text ): return str(text).replace('\\','\\\\').replace("'","\\'")



def new_seq( seqname ):
    """
    Gettting new id from sequence function    
    """    
    try:
        
        query.execute("SELECT nextval('%s'::regclass)" % seqname )
        try:
            return query.fetchone()[0]
        except:
            return False   
        
    except psycopg2.InternalError:
        connection._rollback()   
    
    

def select( q , args = False, fetchone = False, fetchall = True):        
    
    if args:
        if isinstance(args, str) or isinstance(args, long) or isinstance(args, int):
            args = ( args, ) 
        q = query.mogrify( q, args )  
    
    try:        
        query.execute( q )
    except psycopg2.InternalError:
        connection._rollback()
   
    if fetchone:            
         return query.fetchone()
    elif fetchall:
         return query.fetchall()
    else:
        return query.fetchall()
   
    

def insert( table , values, debug = False ):    
    '''   
     
    Collecting sql query for insert. Escaping string. Wraps'em if its needed
    
    sql_insert('user',{'login': 'admin' })    
    
    '''    
    #Query start building
    q = [ "INSERT INTO \"%s\" (" % sql_escape_chars(table).replace('"','') ]; rows = []; val = []
    #Parsing data
    for row, data in values.items():        
        r = row.split("."); row_formed = '"%s"."%s"' % ( r[0], r[1] ) if len(r)==2 else '"%s"' % row
        rows.append( row_formed )  
        if isinstance(data, list) and len(data) == 1:  
            if isinstance( data[0], str):
                
                iAppend = data[0]
            else:
                raise Exception('Данные в неверном формате. %s должен быть только str' % data[0] )   
        else:             
            #if isinstance(data, str) or isinstance(data, int):            
            if match('^\d{1,11}$',str( data )):
                iAppend = str( data )
            else:
                iAppend = "'%s'" % sql_escape_chars(data) if data else "''"
        
        val.append( iAppend )      
    #Collecting data
    q.append( ','.join( rows ) ); q.append(') VALUES ('); q.append(','.join(val)); q.append(');')
    queryString = ''.join(q)
    try:
        if not debug:
            query.execute(queryString)        
            connection.commit()
            return queryString
        else:
            return queryString
    except psycopg2.InternalError:              
        connection._rollback()  
        
        
    
        




def update(table, values, advanced = None):   
    
    """    
    Collecting sql query for update. Escaping string. Wraps'em if it's needed
    
    sql_update('user',{'login': 'admin' }, 'WHERE id = 3')    
    
    """
    
    q = [ "UPDATE \"%s\" SET " % table]; val = []   
         
    for row, data in values.items():             
        r = row.split("."); 
        
        row_formed = '"%s"."%s"' % ( r[0], r[1] ) if len(r)==2 else '"%s"' % row
        
        
        
        if isinstance(data, list) and len(data) == 1:  
            if isinstance( data[0], str):
                
                iAppend = data[0]
            else:
                raise Exception('Данные в неверном формате. %s должен быть только str' % data[0] )   
        else:             
            #if isinstance(data, str) or isinstance(data, int):            
            if match('^\d{1,11}$',str( data )):
                iAppend = str( data )
            else:
                iAppend = "'%s'" % sql_escape_chars(data) if data else "''"
        
           
        val.append( " %s = %s" % ( row_formed,iAppend) )            
    
    q.append(','.join(val))
    
   
    q.append(' ' + advanced) if advanced else ''
    
    queryString = ''.join(q) 
    
    try:
        query.execute(queryString)
        connection.commit()
    except psycopg2.InternalError:        
        connection._rollback()   
        
    
    
        
 